<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"
    import="java.sql.*"
    
    %>
<%
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String url = "jdbc:oracle:thin:@192.168.3.24:1521:xe";
String id = "test";
String pwd = "test";
String driver = "oracle.jdbc.driver.OracleDriver";
String query = "SELECT E.ID,NAMEKOR,DEPTNO, "
    		+ "NVL(POSITIONCODE,' ') AS POSITIONCODE, "
    		+ "NVL(DUTYCODE,' ') AS DUTYCODE, "
    		+ "NVL(CLASSCODE,' ') AS CLASSCODE, "
    		+ "SUBSTR(to_char(APPSTARTDATE,'YYYYMMDD'),0,8) APPSTARTDATE, "
    		+ "SUBSTR(to_char(APPENDDATE,'YYYYMMDD'),0,8) APPENDDATE "
			+ "FROM EMPLOYEE E JOIN APPOINT A ON E.DEPTNO = A.APPDEPTCODE "
			+ "WHERE DEPTNO LIKE '%'||?||'%' "
			+ "AND E.ID LIKE '%'||?||'%' "
			+ "AND NAMEKOR LIKE '%'||?||'%' "		
			+ "ORDER BY E.ID";		
try{
	Class.forName(driver);
	conn = DriverManager.getConnection(url,id,pwd);
	pstmt = conn.prepareStatement(query);
	pstmt.setString(1, request.getParameter("DEPTNO"));
	pstmt.setString(2, request.getParameter("ID"));
	pstmt.setString(3, request.getParameter("NAME"));
	rs = pstmt.executeQuery();
	
	
	System.out.println("{");	
	System.out.println("	DATA:[[");
	
	//JSON 문자열 생성
	out.println("{");	
	out.println("	DATA:[[");
	while(rs.next()){
		//둘중에 하나를 사용할 것.
		//1. 컬럼 순서에 맞춰 데이터 생성
// 		out.println("{C1:'',C2:'',C3:'"+rs.getString("JOB")+"',C4:'"+rs.getString("DEPTNO")+"'");			
// 		out.println(",C5:'"+rs.getString("EMPNO")+"',C6:'"+rs.getString("ENAME")+"',C7:'"+rs.getString("HIREDATE")+"'");
// 		out.println(",C8:'"+rs.getString("MGR")+"',C9:'"+rs.getString("SAL")+"',C10:'"+rs.getString("COMM")+"'},");
		//2. SaveName을 이용한 방법
		out.println("{ID:'"+rs.getString(1)+"',NAMEKOR:'"+rs.getString(2)+"',DEPTNO:'"+rs.getString(3)+"' ");
		out.println(",POSITIONCODE:'"+rs.getString(4)+"',DUTYCODE:'"+rs.getString(5)+"'");
		out.println(",CLASSCODE:'"+rs.getString(6)+"',APPSTARTDATE:'"+rs.getString(7)+"',APPENDDATE:'"+rs.getString(8)+"'},");
		
		System.out.println("{ID:'"+rs.getString(1)+"',NAMEKOR:'"+rs.getString(2)+"',DEPTNO:'"+rs.getString(3)+"' ");
		System.out.println(",POSITIONCODE:'"+rs.getString(4)+"',DUTYCODE:'"+rs.getString(5)+"'");
		System.out.println(",CLASSCODE:'"+rs.getString(6)+"',APPSTARTDATE:'"+rs.getString(7)+"',APPENDDATE:'"+rs.getString(8)+"'},");
		
	}
	out.println("]]}");		

	System.out.println("]]}");	
	
	//JSON 문자열 생성


	

	
	
}catch(Exception ex){
	String msg = ex.getMessage();
	//오류 발생
	out.println("{");
	out.println("Result : {Code:-1, Message:'오류가 발생하였습니다.\n"+msg+"'}");
	out.println("}");
	
}finally{
	rs.close();
	pstmt.close();
	conn.close();	
}
%>

    
    